import pymysql


class DBTools:
    @classmethod
    def __connect_db(cls):
        """创建数据库连接"""
        conn = pymysql.connect(host='211.103.136.244', port=7061,
                               user='student', password='iHRM_student_2022',
                               database='ihrm', charset='utf8')
        return conn

    @classmethod
    def get_one(cls, sql):
        """查询语句，获取单条数据"""
        conn = None
        cursor = None
        res = None

        try:
            # 创建连接
            conn = cls.__connect_db()
            # 创建游标
            cursor = conn.cursor()
            # 执行 sql 语句
            cursor.execute(sql)
            # 获取结果
            res = cursor.fetchone()
        except Exception as e:
            print('发生了错误: ', e)
        finally:
            # 关闭游标
            cursor.close()
            # 关闭连接
            conn.close()
            # 返回结果
            return res

    @classmethod
    def uid_db(cls, sql):
        """增删改操作"""
        conn = None
        cursor = None

        try:
            # 创建连接
            conn = cls.__connect_db()
            # 创建游标
            cursor = conn.cursor()
            # 执行 sql 语句
            res = cursor.execute(sql)
        except Exception as e:
            # 发生了错误 回滚
            conn.rollback()
            print('发生了错误: ', e)
        else:
            # 没发生错误 提交
            conn.commit()
        finally:
            # 关闭游标
            cursor.close()
            # 关闭连接
            conn.close()


if __name__ == '__main__':
    add_sql = 'insert into bs_user(id, username, mobile, work_number) ' \
              'values("12345678901234568", "李四", "1660000060", "0001");'
    DBTools.uid_db(add_sql)

    get_sql = 'select id from bs_user where mobile = "1660000060";'
    id = DBTools.get_one(get_sql)
    print(id)
